PostgreSQL Debug

• just running EXPLAIN SELECT ... only gives estimates, EXPLAIN ANALYZE SELECT ... runs the query and provides the “real” cost
• use EXPLAIN (ANALYZE, BUFFERS) SELECT ... gives some more information
  • extreme version is EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON) SELECT ...
• visualizing EXPLAIN: https://explain.dalibo.com/
• enable pg_stat by setting the following configuration options:
  • shared_preload_libraries=pg_stat_statements
  • pg_stat_statements.track=all
• run ANALYZE on tables after bulk imports, this updates the estimates for the query planer (so indexes are correctly used)
• compare Data in different tables, including with other types: https://github.com/CrunchyData/pgCompare

sources

• https://mattermost.com/blog/making-a-postgres-query-1000-times-faster/
• https://render.com/blog/postgresql-slow-query-to-fast-via-stats